import pandas as pd
import os
import xpinyin

pwd = os.path.dirname(os.path.dirname(__file__))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'shop.settings')
import django

django.setup()


def get_s(con):
    p = xpinyin.Pinyin()
    result = [p.get_initials(a, '') for a in [con]][0]
    return result


from goods.models import Good, GoodCategory, GoodBrand, GoodTags, Source, Product, SPG


def gen_source():
    source = Source()
    source.name = 'cosme-de'
    source.url = 'https://www.cosme-de.net/'
    source.save()

    source = Source()
    source.name = 'petpochitto'
    source.url = 'https://www.petpochitto.com/'
    source.save()

    source = Source()
    source.name = 'superdelivery'
    source.url = 'https://www.superdelivery.com/'
    source.save()


def read():
    # cate1
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            category2 = GoodCategory()
            category3 = GoodCategory()
            good_brand = GoodBrand()
            good_tag = GoodTags()

            # time.sleep(1)
            en_brand = row['en_brand_y']
            jp_brand = row['jp_brand_x']

            cate1 = row['cate1_x']
            cate2 = row['cate2_x']
            cate3 = row['cate3_x']
            cate1_zh = row['cate1中文']
            cate2_zh = row['cate2中文']
            cate3_zh = row['cate3中文']
            cate1_py = get_s(cate1)
            cate2_py = get_s(cate2)
            cate3_py = get_s(cate3)

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)
            print(s1, cate1)
            if s1.first():
                category1 = s1.first()
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 1
                category1.parent_category = None
                category1.save()

            # 一级分类插入品牌
            g1 = GoodBrand.objects.filter(en_name=en_brand)
            if g1.first():
                good_brand = g1.first()
            else:
                good_brand.category = category1
                good_brand.en_name = en_brand
                good_brand.jp_name = jp_brand
                good_brand.save()
            # 二级
            s2 = GoodCategory.objects.filter(name=cate2)
            if s2.first():
                category2 = s2.first()
            else:
                category2.name = cate2
                category2.modify_name = cate2_zh
                category2.code = cate2_py
                category2.category_type = 2
                category2.parent_category = category1
                category2.save()
            # 三级
            s3 = GoodCategory.objects.filter(name=cate3)
            if s3.first():
                category3 = s3.first()
            else:
                category3.name = cate3
                category3.code = cate3_py
                category3.modify_name = cate3_zh
                category3.category_type = 3
                category3.parent_category = category2
                category3.save()

            print(cate1, cate2, cate3, cate1_py, cate2_py, cate3_py)

            tags = eval(row['tags'])

            for tag in tags:
                t1 = GoodTags.objects.filter(jp_name=tag).first()
                if t1:
                    good_tag = t1
                else:
                    good_brand.en_name = ''
                    good_brand.jp_name = tag
                    good_brand.save()

            url = row['url']
            j1 = Good.objects.filter(good_url=url)
            if j1.first():
                pass
            else:
                belong = row['belong']
                skus = eval(row['skus'])
                for sku in skus:
                    gd = Good()
                    name = sku['name']
                    brand = sku['brand']

                    market_price = sku['market_price']

                    content = sku['content']
                    description = sku['description']
                    comments = sku['comments']
                    sku_value = sku['sku']
                    status = sku['status']
                    sold_price = sku['sold_price']
                    image = sku['image']
                    rate = sku['rate']

                    gd.category = category3
                    gd.good_sn = sku_value
                    gd.name = name
                    gd.cover = image[0]
                    gd.good_num = 100000
                    gd.sold_num = 0
                    gd.click_num = 0
                    gd.fav_num = 0
                    gd.good_desc = description
                    gd.market_price = market_price
                    gd.shop_price = sold_price
                    gd.good_detail = content
                    gd.is_fare = 0
                    gd.pinpai = en_brand
                    gd.tags = tags
                    gd.good_url = url
                    gd.belong = belong
                    gd.jp_pinpai = brand
                    gd.good_status = status
                    gd.comments = comments
                    gd.rate = rate
                    gd.save()

                    for goods_image in image:
                        print(goods_image)
                        goods_image_instance = GoodCarouselImage()
                        goods_image_instance.image = goods_image
                        goods_image_instance.good = gd
                        goods_image_instance.save()

        except Exception as e:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(row) + '\n')
            print(e)
            continue


def read_category1():
    # cate1
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate1 = row['cate1_x']
            cate1_zh = row['cate1中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 1
                category1.parent_category = None
                category1.save()


        except Exception as e:
            with open('cosme1.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def read_category2():
    # cate1
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate = row['cate1_x']
            cate1 = row['cate2_x']
            cate1_zh = row['cate2中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 2
                category1.parent_category = GoodCategory.objects.get(name=cate)
                category1.save()


        except Exception as e:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def read_category3():
    # cate1
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate = row['cate2_x']
            cate1 = row['cate3_x']
            cate1_zh = row['cate3中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 3
                category1.parent_category = GoodCategory.objects.get(name=cate)
                category1.save()


        except Exception as e:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def brand():
    import random
    df = pd.read_excel(r'cos22.xlsx')
    bs = [
        'https://kaola-haitao.oss.kaolacdn.com/ii02po4970_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ii02shtz66_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1c2gohbio65_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1cnij2nrr52_300_300.jpg',
        'http://kaola-haitao.oss.kaolacdn.com/ir1s6rzz67_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1bk8ndmup45_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1el9nv9cj24_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ifxc1wa478_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1c126l96j31_300_300.jpg',
        'http://kaola-haitao.oss.kaolacdn.com/1btlvtbrr35_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ieww1xa027_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ig28fghh45_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ix2kwjyf95_300_300.jpg'
    ]
    for item, row in df.iterrows():
        try:
            cate = row['cate1_x']
            en = row['en_brand_y']
            jp = row['jp_brand_x']
            b1 = GoodBrand.objects.filter(en_name=en).first()
            if b1:
                pass
            else:
                brand = GoodBrand()
                brand.category = GoodCategory.objects.filter(category_type=1).get(name=cate)
                brand.jp_name = jp
                brand.en_name = en
                brand.letter = str(en)[:1].upper() if len(str(en)) > 1 else '#'
                brand.image = random.choice(bs)
                brand.save()
        except:
            with open('cosme_brand.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


import json


def json_img(a):
    k = []
    for item in a:
        for i in item:
            k.append(i)

    return json.dumps({'images': k})


def good():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            cate = row['cate3_x']
            en = row['en_brand_y']
            sn = row['pid_x']
            title_x = row['title_x']
            url = row['url']
            sku = eval(row['skus_x'])[0]
            good = Product()

            # tags = eval(row['tags_x'])
            # for tag in tags:
            #
            #     good.tag = good.tag.add([GoodTags.objects.get(jp_name=x) for x in tags])
            # good.spg = SPG.objects.get(name='测试')
            good.source = Source.objects.get(name='cosme-de')
            # bug 修复  控制分类范围
            good.category = GoodCategory.objects.filter(category_type=3).get(name=cate)
            good.brand = GoodBrand.objects.get(en_name=en)
            good.sn = sn
            good.title = title_x
            good.desc = sku['description']
            good.detail = sku['content']
            imgs = [x['image'] for x in eval(row['skus_x'])]
            good.images = json_img(imgs)
            good.third_url = url
            good.save()
            # print(imgs)


        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good1():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            sn = row['pid_x']
            pro = Product.objects.get(sn=sn)
            sku = eval(row['skus_x'])
            for item in sku:
                good = Good()
                good.be_product = pro
                good.classify = item['name']
                good.sku_id = item['sku']
                # 这里要切割一下
                good.good_type = item['name']
                # 化妆品为空
                good.good_model = ''
                good.status = 0 if item['status'] == 'InStock' else 1
                good.cover = item['image'][0]
                good.shop_price = item['sold_price']
                good.market_price = item['market_price']
                good.save()



        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


from hashlib import md5


def request_params_md5(p):
    m = md5()
    m.update(str(p).encode('utf-8'))
    return m.hexdigest()


def good_tag():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            cate = row['pid_x']
            good = Good.objects.get(sn=cate)
            tags = eval(row['tags_x'])
            for tag in tags:
                l = GoodTags.objects.get(jp_name=tag)
                good.tag = good.tag.add(l)
                good.save()

        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good_param():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            pid = row['pid_x']
            sku = eval(row['skus_y'])
            url = row['url']
            kk = Good.objects.get(sn=pid)
            for sk in sku:
                # print(sk)
                param = GoodParam()
                param.be_good = kk
                param.classify = sk['attr_name']
                param.sku_id = request_params_md5(sk['sku'] + url)
                param.good_model = ''
                param.status = 1 if sk['status'] == 'InStock' else 0
                param.cover = sk['image'][0]
                param.shop_price = sk['sold_price']
                param.market_price = sk['market_price']
                param.save()


        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def tag():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            tags = eval(row['tags_x'])
            for item in tags:
                t = GoodTags.objects.filter(jp_name=item).first()
                if t:
                    pass
                else:
                    tag = GoodTags()
                    tag.en_name = ''
                    tag.jp_name = item
                    tag.save()
        except:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good_image():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            pid = row['pid_x']
            sku = eval(row['skus_y'])
            kk = Good.objects.get(sn=pid)
            # print(sku)
            for goods_image in sku:
                imgs = goods_image['image']
                for img in imgs:
                    goods_image_instance = GoodCarouselImage()
                    goods_image_instance.image = img
                    goods_image_instance.good = kk
                    goods_image_instance.save()

        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


if __name__ == '__main__':
    # gen_source()
    # read_category3()
    # read_category3()
    # brand()
    good1()
    # tag()
    # good()
    # good_tag()
    # good_param()
    # good_image()
